package com.example.demo.com.java8;

import com.alibaba.fastjson.JSON;
import com.opencsv.CSVParser;
import com.opencsv.CSVReader;
import org.apache.commons.lang3.StringUtils;
import sun.misc.BASE64Encoder;

import java.io.*;
import java.net.HttpURLConnection;
import java.net.URL;
import java.sql.*;
import java.util.*;


/**
 * Created by yanxiao1 on 2019/3/12.
 */
public class Test {
    private static Map<String,String> map = new HashMap<>(1250);
    public static void main(String[] args) throws IOException {
        //处理中票用户的csv文件
        readCsv();
        try {
            //处理注册用户的csv文件(这块文件到时候换成具体的文件)
            DataInputStream in = new DataInputStream(new FileInputStream(new File("D:\\59622964.csv")));
            //从第0行记录读取，这块主要看实际情况，如果数据不对就从第一行数据读，把0改为1就可以
            CSVReader csvReader = new CSVReader(new InputStreamReader(in, "GBk"), CSVParser.DEFAULT_SEPARATOR,
                    CSVParser.DEFAULT_QUOTE_CHARACTER, CSVParser.DEFAULT_ESCAPE_CHARACTER, 0);
            String[] strs;
            List<String> list = new ArrayList<>();
            while ((strs = csvReader.readNext()) != null) {
                String csv = Arrays.deepToString(strs);
                list.add(csv.substring(1,csv.length()-1));
            }

            List<List<String>> list2 = new ArrayList<>();
            for (String s : list) {
                String[] split = s.split(",");
                List<String> list1 = new ArrayList<>();
                for (String s1 : split) {
                    list1.add(s1.trim());
                }
                if(StringUtils.isNotBlank(map.get(list1.get(1)))){
                    list2.add(list1);
                }
            }
            updateSqlite(list2);
            csvReader.close();

        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    private static void  readCsv(){
        try {
            DataInputStream in = new DataInputStream(new FileInputStream(new File("D:\\59622965.csv")));
            CSVReader csvReader = new CSVReader(new InputStreamReader(in, "GBK"), CSVParser.DEFAULT_SEPARATOR,
                    CSVParser.DEFAULT_QUOTE_CHARACTER, CSVParser.DEFAULT_ESCAPE_CHARACTER, 0);
            String[] strs;
            List<String> list = new ArrayList<>();
            while ((strs = csvReader.readNext()) != null) {
                String csv = Arrays.deepToString(strs);
                list.add(csv.substring(1,csv.length()-1));
            }

            List<List<String>> list2 = new ArrayList<>();
            for (String s : list) {
                List<String> list1 = new ArrayList<>();
                String[] split = s.split(",");
                for (String s1 : split) {
                    list1.add(s1.trim());
                }
                list2.add(list1);
                map.put(list1.get(1),list1.get(1));
            }
            updateSqlite2(list2);
            csvReader.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    /**
     * 更新sqlite数据库
     *
     * @return
     */
    private static void updateSqlite(List<List<String>> list2) {
        Connection connection = null;

        try {
            Class.forName("org.sqlite.JDBC");
            connection = DriverManager.getConnection("jdbc:sqlite:D:\\visual studio 2015\\Projects\\avg\\WindowsFormsApplication5\\bin\\Debug\\MyDatabase.db");
            for (List<String> list : list2) {
                String s = list.get(9);
//                if(!s.contains("jfs")){
//                    continue;
//                }
                //创建sqlite的连接

                if (null == connection) {
                    return;
                }
                //关闭事务自动提交
                connection.setAutoCommit(false);

                Statement statement = connection.createStatement();
//                String query = "select open_id from sign_user where open_id = '"+list.get(1)+"'";
//                ResultSet resultSet = statement.executeQuery(query);
//                if(resultSet.next() && resultSet.getBlob("img_native") != null){
//                    continue;
//                }
//                if(!"ortYy1vSY8V2FUKkORQg0CAOZ0k4".equals(list.get(1))){
//                    continue;
//                }
                String base64 = imageBase(s);
                //sql语句，根据openid 更新数据库信息，添加base64位文件
                String sql = "replace INTO sign_user(id, open_id, u_name, phone, birth_date," +
                        "img_native)" +
                        "VALUES("+list.get(0)+","+"'"+list.get(1)+"'"+","+"'"+list.get(4)+"'"
                        +","+"'"+list.get(5)+"'"+","+"'"+list.get(8)+"'"+","+"'"+base64+"'"+")";
                //更新数据库数据
                statement.executeUpdate(sql);
                connection.commit();
                statement.close();

            }
            connection.close();//关闭数据库连接
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException e) {

            }
        }
    }

    private static void updateSqlite2(List<List<String>> list2) {
        Connection connection = null;

        try {
            //创建sqlite的连接
            Class.forName("org.sqlite.JDBC");
            connection = DriverManager.getConnection("jdbc:sqlite:D:\\visual studio 2015\\Projects\\avg\\WindowsFormsApplication5\\bin\\Debug\\MyDatabase.db");
            for (List<String> list : list2) {


                if (null == connection) {
                    return;
                }
                //关闭事务自动提交
                connection.setAutoCommit(false);
                Statement statement = connection.createStatement();
                //sql语句，根据openid 更新数据库信息，添加base64位文件

                String sql = "replace INTO seckill_result(id, open_id, ticket_detail_id,seat, sec_time, sec_ip, modified, created, yn)  " +
                        "VALUES("+list.get(0)+","+"'"+list.get(1)+"'"+","+"'"+list.get(2)+"'"+","+"'"+list.get(3)+"'"+","+"'"+list.get(4)+"'"
                        +","+"'"+list.get(5)+"'"+","+"'"+list.get(8)+"'"+","+"'"+list.get(9)+"'"+","+"'"+list.get(10)+"')";
                //更新数据库数据
                statement.executeUpdate(sql);
                connection.commit();
                statement.close();
            }
        } catch (Exception e) {
            e.printStackTrace();
            return;
        } finally {
            try {
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException e) {
            }
        }
    }
    /**
     * base64位文件转换
     * @param imageUrl
     * @return
     */
    private static String imageBase(String imageUrl) {
        ByteArrayOutputStream data = new ByteArrayOutputStream();
        try {
            //必须加http
            String imgFile = "http://img30.360buyimg.com/pop/" + imageUrl;
            // 创建URL
            URL url = new URL(imgFile);
            // 创建链接
            HttpURLConnection conn = (HttpURLConnection) url.openConnection();
            conn.setRequestMethod("GET");
            conn.setConnectTimeout(5000);
            InputStream is = conn.getInputStream();
            byte[] by = new byte[1024];
            // 将内容放到内存中
            int len = -1;
            while ((len = is.read(by)) != -1) {
                data.write(by, 0, len);
            }
            is.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
        // 对字节数组Base64编码
        BASE64Encoder encoder = new BASE64Encoder();
        return encoder.encode(data.toByteArray());
    }



}
